/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sys.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sys.bean.*;
import sys.utils.ConexaoADS;
import sys.utils.ConexaoMySql;
import sys.utils.Utils;
import static sys.utils.Utils.stringToDate;

/**
 *
 * @author Cesar Daniel
 */
public class PedidoDao {

    /*
     INICIO METODOS CONCRETEIRA    
     */
//    /*
//     atualizando status de pedido agendado
//     */
//    public void alteraStatusPedido(int nPedido) throws SQLException {
//
//        Connection con = ConexaoMySql.getConection();
//
//        String sql = "UPDATE pedido\n"
//                + "SET agendado=? \n"
//                + "WHERE id_pedido=?;";
//
//        PreparedStatement stmt = con.prepareStatement(sql);
//
//        stmt.setString(1, "S");
//        stmt.setInt(2, nPedido);
//
//        stmt.execute();
//
//        stmt.close();
//        con.close();
//
//    }

    /*
     verificando se pedido ja existe no mysql
     */
    public static boolean verExistePedido(int nPedido) throws SQLException {
        boolean v = false;

        Connection con = ConexaoMySql.getConection();

        String sql = "SELECT p.id_pedido \n"
                + "FROM pedido p \n"
                + "WHERE p.id_pedido = " + nPedido;

        PreparedStatement stmt = con.prepareStatement(sql);

        ResultSet rs = stmt.executeQuery();

        if (rs.next()) {
            v = true;
        }

        stmt.close();
        con.close();

        return v;

    }

    public void insertPedidoMySql(String nPedido) throws SQLException, IOException {

        PedidoBean pedido = getPedido(nPedido);

        Connection con = ConexaoMySql.getConection();
        PreparedStatement stmt;

        String sql = "INSERT INTO pedido \n"
                + "(id_pedido, codigo_usuario, id_cliente, data, valor_total, desconto_gerente, desconto_especial, desconto_f_pgto, acres_gerente, acres_f_pgto, tipo) \n"
                + "VALUES \n"
                + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        stmt = con.prepareStatement(sql);

        stmt.setInt(1, Integer.parseInt(nPedido));
        stmt.setString(2, pedido.getUsuario().getCodigo());
        stmt.setInt(3, Integer.parseInt(pedido.getCliente().getCodigo()));
        stmt.setDate(4, Utils.convert(stringToDate(pedido.getData())));
        stmt.setDouble(5, pedido.getvTotal());
        stmt.setDouble(6, pedido.getdGerente());
        stmt.setDouble(7, pedido.getdEspecial());
        stmt.setDouble(8, pedido.getdFormaPgto());
        stmt.setDouble(9, pedido.getaGerente());
        stmt.setDouble(10, pedido.getaFormaPgto());
        stmt.setString(11, "P");
 
        stmt.execute();

        stmt.close();
        con.close();
    }

    public static PedidoBean getPedido(String nPedido) throws SQLException, IOException {
        PedidoBean pedido = new PedidoBean();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13NPED, C13VEND, C13CLIE, C13DATA, C13VRTOT, C13DESCGER, C13VDESESP, C13VDESFPG, C13ACREGER, C13VACRFPG FROM SPAC13 \n"
                + "WHERE C13NPED = '" + nPedido + "'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            pedido.setPedido(rs.getString("C13NPED"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setCodigo(rs.getString("C13VEND"));
            pedido.setUsuario(usuario);

            ClienteBean cliente = new ClienteBean();
            cliente.setCodigo(rs.getString("C13CLIE"));
            pedido.setCliente(cliente);

            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));
            pedido.setvTotal(rs.getDouble("C13VRTOT"));
            pedido.setdGerente(rs.getDouble("C13DESCGER"));
            pedido.setdEspecial(rs.getDouble("C13VDESESP"));
            pedido.setdFormaPgto(rs.getDouble("C13VDESFPG"));
            pedido.setaGerente(rs.getDouble("C13ACREGER"));
            pedido.setaFormaPgto(rs.getDouble("C13VACRFPG"));
        }

        stmt.close();
        con.close();

        return pedido;
    }

    /*
     metodo utilizado para listar os pedidos com itens trabalhados na concreteira
     */
    public static List<PedidoBean> vendaProdutosConcreteira(String dataInicial, String dataFinal, String codProduto) throws SQLException, IOException {

        List<PedidoBean> listaPedidos = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, C13NPED, C05CODIGO, C05NOME, C04NOME \n"
                + "FROM SPAC13 \n"
                + "INNER JOIN SPAC14 ON (C13NPED = C14NPED) \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C13CLIE) \n"
                + "WHERE C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C14PROD = '" + Utils.codCorreto(codProduto, 5) + "'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));
            pedido.setPedido(rs.getString("C13NPED"));

            ClienteBean cliente = new ClienteBean();
            cliente.setCodigo(rs.getString("C05CODIGO"));
            cliente.setNome(rs.getString("C05NOME"));

            pedido.setCliente(cliente);

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));

            pedido.setUsuario(usuario);

            listaPedidos.add(pedido);
        }

        stmt.close();
        con.close();

        return listaPedidos;

    }
    /*
     FIM METODOS CONCRETEIRA
     */

    /*
     totalizando valores de acrescimo de boleto
     */
    public static List<PedidoBean> acrescimoBoletoEmpresaDiario(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, SUM(C13ACREGER) AS ACRECIMO \n"
                + "FROM SPAC13 \n"
                + "WHERE C13ACREGER IN (3, 6, 9, 12, 15, 18, 21, 24, 27, 30) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));
            pedido.setaGerente(rs.getDouble("ACRECIMO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de acrescimo de boleto
     */
    public static List<PedidoBean> acrescimoBoletoEmpresaMensal(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, SUBSTRING(C13DATA,1,4) AS ANO, SUM(C13ACREGER) AS ACRECIMO \n"
                + "FROM SPAC13 \n"
                + "WHERE C13ACREGER IN (3, 6, 9, 12, 15, 18, 21, 24, 27, 30) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' "
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setMes(rs.getString("MES"));
            pedido.setAno(rs.getString("ANO"));
            pedido.setaGerente(rs.getDouble("ACRECIMO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de acrescimo de boleto
     */
    public static List<PedidoBean> acrescimoBoletoMensal(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, C04NOME, SUM(C13ACREGER) AS ACRECIMO \n"
                + "FROM SPAC13 \n"
                + "JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "WHERE C13ACREGER IN (3, 6, 9, 12, 15, 18, 21, 24, 27, 30) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setSobreNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            pedido.setaGerente(rs.getDouble("ACRECIMO"));
            pedido.setData(rs.getString("MES"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de acrescimo de boleto
     */
    public static List<PedidoBean> acrescimoBoletoDiario(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, C04NOME, SUM(C13ACREGER) AS ACRECIMO \n"
                + "FROM SPAC13 \n"
                + "JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "WHERE C13ACREGER IN (3, 6, 9, 12, 15, 18, 21, 24, 27, 30) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setSobreNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            pedido.setaGerente(rs.getDouble("ACRECIMO"));
            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de desconto no recebimento por data
     */
    public static List<PedidoBean> descontoRecebimentoVendedorDiario(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C12DTREC, C04NOME, SUM(C12VRPAR-C12VRPGTO) AS DESCONTO \n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C12FLGREC = 'P' \n"
                + "AND C12VRPAR-C12VRPGTO > 0 \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setSobreNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            //nao é a forma correta
            pedido.setData(Utils.dataCorreta(rs.getString("C12DTREC")));
            pedido.setdGerente(rs.getDouble("DESCONTO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de desconto no recebimento
     */
    public static List<PedidoBean> descontoRecebimentoEmpresaDiario(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C12DTREC, SUM(C12VRPAR-C12VRPGTO) AS DESCONTO \n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "WHERE  C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C12FLGREC = 'P' \n"
                + "AND C12VRPAR-C12VRPGTO > 0 \n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            pedido.setData(Utils.dataCorreta(rs.getString("C12DTREC")));
            pedido.setdGerente(rs.getDouble("DESCONTO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de desconto no recebimento
     */
    public static List<PedidoBean> descontoRecebimentoEmpresaMensal(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C12DTREC,5,2) AS MES, SUBSTRING(C12DTREC,1,4) AS ANO, SUM(C12VRPAR-C12VRPGTO) AS DESCONTO \n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "WHERE  C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C12VRPAR-C12VRPGTO > 0 \n"
                + "AND C12FLGREC = 'P' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            pedido.setMes(rs.getString("MES"));
            pedido.setAno(rs.getString("ANO"));
            pedido.setdGerente(rs.getDouble("DESCONTO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }


    /*
     totalizando valores de desconto no recebimento
     */
    public static List<PedidoBean> descontoRecebimentoVendedorMensal(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C12DTREC,5,2) AS MES, C04NOME,  SUM(C12VRPAR-C12VRPGTO) AS DESCONTO \n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE  C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C12FLGREC = 'P' \n"
                + "AND C12VRPAR-C12VRPGTO > 0 \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setSobreNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            pedido.setData(rs.getString("MES"));
            pedido.setdGerente(rs.getDouble("DESCONTO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de desconto no recebimento
     */
    public static List<PedidoBean> descontoRecebimento(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME, SUM(C12VRPAR-C12VRPGTO) AS DESCONTO \n"
                + "FROM SPAC12 \n"
                + "INNER JOIN SPAC13 ON (C12NRDOC = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE  C12DTREC BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C12FLGREC = 'P' \n"
                + "AND (C12VRPAR-C12VRPGTO) > 0 \n"
                + "AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            pedido.setdGerente(rs.getDouble("DESCONTO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     totalizando valores de acrescimo de boleto
     */
    public static List<PedidoBean> acrescimoBoleto(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<PedidoBean> listaAcrescimo = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME, SUM(C13ACREGER) AS ACRECIMO \n"
                + "FROM SPAC13 \n"
                + "JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "WHERE C13ACREGER IN (3, 6, 9, 12, 15, 18, 21, 24, 27, 30) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);
            pedido.setaGerente(rs.getDouble("ACRECIMO"));

            listaAcrescimo.add(pedido);
        }

        stmt.close();
        con.close();

        return listaAcrescimo;
    }

    /*
     retornando total de gastos do cliente nos ultimos meses
     */
    public static List<PedidoBean> gastosClientePeriodo(String codCliente, String dataInicial, String dataFinal) throws SQLException, IOException {
        List<PedidoBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, SUM(ROUND(C13VRTOT + ( C13ACREGER+C13VACRFPG) - ( C13DESCGER+C13VRVALE+C13VDESFPG+C13VDESESP),2)) AS TOTAL \n"
                + "FROM SPAC13 \n"
                + "WHERE C13CLIE = '" + codCliente + "' \n"
                + "AND C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "'\n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();
            pedido.setData(rs.getString("MES"));
            pedido.setvTotal(rs.getDouble("TOTAL"));

            lista.add(pedido);
        }

        stmt.close();
        con.close();

        return lista;
    }

    /*
     totalizando venda por vendedor, período, grupo e subgrupo - Analitico, apenas valores
     */
    public static List<PedidoBean> vendaGrupoSubGrupoAnalitico(String dataInicial, String dataFinal, String vendedor, String grupo, String subgrupo) throws SQLException, IOException {
        List<PedidoBean> listaVendas = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME, SUM(C14QTD*C14VRUNIT) AS TOTAL \n"
                + "FROM SPAC01 \n"
                + "INNER JOIN SPAC14 ON (C01CODIGO = C14PROD) \n"
                + "INNER JOIN SPAC13 ON (C14NPED = C13NPED) \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "WHERE \n"
                + "C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C01GRUPO LIKE '%" + grupo + "%' \n"
                + "AND C01SUB LIKE '%" + subgrupo + "%' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "GROUP BY 1 \n"
                + "ORDER BY 2 DESC";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();
            pedido.setvFinal(rs.getDouble("TOTAL"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));

            pedido.setUsuario(usuario);
            listaVendas.add(pedido);
        }

        stmt.close();
        con.close();
        return listaVendas;
    }


    /*
     alterando vendedor
     */
    public static void alterarVendedor(UsuarioBean usuario, String pedido) throws SQLException, IOException {

        Connection con = ConexaoADS.getConection();

        String sql = "UPDATE SPAC13 SET C13VEND = ? \n"
                + "WHERE C13NPED = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, usuario.getCodigo());
        stmt.setString(2, pedido);

        stmt.execute();

        stmt.close();
        con.close();

    }

    /*
     retornando a quantidade de pedidos do período
     */
    public static List<PedidoBean> qtdPedidos(String dataInicial, String dataFinal, String vendedor, String fPagto, String tPedido, String param) throws SQLException, IOException {
        List<PedidoBean> listaQtdPedidos = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String complementoSQL = "\n";

        if (param.equals("005")) {
            complementoSQL = "AND USUFUNCAO = '005'\n";
        }

        String sql = "SELECT USUNOME, COUNT(C13NPED) AS QTD \n"
                + "FROM SPAC13 "
                + "INNER JOIN SPAC10 ON (C13FPAGTO = C10CODIGO) \n"
                + "INNER JOIN SPUSU ON (C13VEND = USUCODIGO) \n"
                + "INNER JOIN SPFUSU ON (USUFUNCAO = FUCODIGO) \n"
                + "WHERE \n"
                + "C13TIPO = '" + tPedido + "' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' "
                + "AND USUCODIGO LIKE '%" + vendedor + "%' \n"
                + "AND C13FPAGTO LIKE '%" + fPagto + "%' \n"
                + complementoSQL
                + "GROUP BY 1 \n";
        //+ "ORDER BY 2 DESC";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();
            pedido.setQtdPedidos(rs.getInt("QTD"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("USUNOME"));

            pedido.setUsuario(usuario);
            listaQtdPedidos.add(pedido);
        }

        stmt.close();
        con.close();

        return listaQtdPedidos;

    }

    /*
     * lista com total de custo, total de venda e nome do vendedor. soma tudo e
     * depois acrescenta os descontos e acrescimos para fazer o calcula da
     * lucratividade
     */
    public static List<PedidoBean> totalCusto(String dataInicial, String dataFinal, String vendedor, String fPgto, String tPedido) throws SQLException, IOException {

        List<PedidoBean> listaPedido = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT USUNOME, SUM(CUSTO) AS CUSTO FROM (SELECT USUNOME, SUM(C93CUSTO*(C93QTDE-C93ENTREG)) AS CUSTO \n"
                + "    FROM SPAC93 \n"
                + "    INNER JOIN SPAC13 ON (C93NPED = C13NPED) \n"
                + "    INNER JOIN SPUSU ON (C13VEND = USUCODIGO) \n"
                + "    INNER JOIN SPFUSU ON (USUFUNCAO = FUCODIGO) \n"
                //+ "	INNER JOIN SPAC10 ON (C10CODIGO = C13FPAGTO) \n"
                + "		WHERE C13TIPO = '" + tPedido + "' AND C13SITU = '' \n"
                + "		AND C13VEND LIKE '%" + vendedor + "%' \n"
                //  + "		AND C10CODIGO LIKE '%" + fPgto + "%' \n"
                + "		AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "'\n"
                + "    GROUP BY 1\n"
                + "UNION \n"
                + "SELECT USUNOME, SUM(C08CUSTO*C08QTDPRO) AS CUSTO \n"
                + "     FROM SPAC08\n"
                + "     INNER JOIN SPAC13 ON (C08NRDOC = C13NPED) \n"
                + "	INNER JOIN SPUSU ON (C13VEND = USUCODIGO) \n"
                + "	INNER JOIN SPFUSU ON (USUFUNCAO = FUCODIGO) \n"
                // + "	INNER JOIN SPAC10 ON (C10CODIGO = C13FPAGTO) \n"
                + "		WHERE C13TIPO = '" + tPedido + "' AND C13SITU = '' \n"
                + "		AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "             AND C08TIPO IN ('21','31') \n"
                //  + "		AND C10CODIGO LIKE '%" + fPgto + "%' \n"
                + "		AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "    GROUP BY 1) PRINCIPAL \n"
                + "GROUP BY USUNOME";
        System.out.println(sql);

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            UsuarioBean user = new UsuarioBean();
            user.setNome(rs.getString("USUNOME"));

            PedidoBean pedido = new PedidoBean();
            pedido.setUsuario(user);
            pedido.setvCusto(rs.getDouble("CUSTO"));

            listaPedido.add(pedido);

        }

        stmt.close();
        con.close();

        return listaPedido;
    }

    //Lista com os dados para atualizar a tabela
    public static List<PedidoBean> vendasTabela(String dataInicial, String dataFinal, String vendedor, String fPgto, String tPedido) throws SQLException, IOException {

        List<PedidoBean> listaPedido = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13NPED, C13DATA, C04NOME, C05CODIGO, C05NOME, C10DESC,  C13VRTOT AS VTOTAL, \n"
                + "C13DESCGER AS DESCGER, C13VDESFPG AS DESCFPGTO, C13VDESESP AS DESCESPECIAL, \n"
                + "C13ACREGER AS ACREGER, C13VACRFPG AS ACRFPGTO, \n"
                + "C13VRVALE AS VALE, \n"
                + "C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = C13VEND) \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C13CLIE) \n"
                + "INNER JOIN SPAC10 ON (C10CODIGO = C13FPAGTO) \n"
                + "WHERE  \n"
                + "C13TIPO = '" + tPedido + "' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "AND C10CODIGO LIKE '%" + fPgto + "%' \n"
                + "ORDER BY 14 DESC";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            pedido.setPedido(rs.getString("C13NPED"));
            pedido.setData(rs.getString("C13DATA"));
            pedido.setvTotal(rs.getDouble("VTOTAL"));
            pedido.setdGerente(rs.getDouble("DESCGER"));
            pedido.setdFormaPgto(rs.getDouble("DESCFPGTO"));
            pedido.setdEspecial(rs.getDouble("DESCESPECIAL"));
            pedido.setaGerente(rs.getDouble("ACREGER"));
            pedido.setaFormaPgto(rs.getDouble("ACRFPGTO"));
            pedido.setVale(rs.getDouble("VALE"));
            pedido.setvFinal(rs.getDouble("VRFINAL"));

            UsuarioBean usuario = new UsuarioBean();

            usuario.setNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);

            ClienteBean cliente = new ClienteBean();

            cliente.setNome(rs.getString("C05NOME"));
            cliente.setCodigo(rs.getString("C05CODIGO"));
            pedido.setCliente(cliente);

            FormaPgtoBean fPgtob = new FormaPgtoBean();
            fPgtob.setDescricao(rs.getString("C10DESC"));
            pedido.setfPgto(fPgtob);

            listaPedido.add(pedido);
        }

        stmt.close();
        con.close();

        return listaPedido;
    }

    /*
     * lista com valores para atualizar o gráfico e total de valores e tambem fornece valores para serem utilizados na rentabilidade.
     variavel paramGrafico: parametro que vem da tela de vendas, pode trazer todos que efetuaram vendas para atalizar o grafico, 
     somente os vendedores ou somente os usuários que não forem vendedores. opção utilizada porque se aparecer muitos usuários no gráfico fica feio
     *
     */
    public static List<PedidoBean> vendasGrafico(String dataInicial, String dataFinal, String vendedor, String tPedido) throws SQLException, IOException {

        List<PedidoBean> listaVendas = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME, SUM(C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER) AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE \n"
                + "C13TIPO = '" + tPedido + "' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C13VEND LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1 \n"
                + "ORDER BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();

            pedido.setvFinal(rs.getDouble("VRFINAL"));

            UsuarioBean usuario = new UsuarioBean();

            usuario.setNome(rs.getString("C04NOME"));
            pedido.setUsuario(usuario);

            listaVendas.add(pedido);

        }

        stmt.close();
        con.close();

        return listaVendas;
    }

    /*
     atualiza grafico com comparativo DIARIO entre vendedores por período
     */
    public static List<PedidoBean> grafMediaDiariaVendedor(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {

        List<PedidoBean> listaSequencia = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, C04NOME, SUM(C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER) AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE \n"
                + "C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "GROUP BY 1, 2  ";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));

            pedido.setvFinal(rs.getDouble("VRFINAL"));

            UsuarioBean usuario = new UsuarioBean();

            usuario.setNome(rs.getString("C04NOME"));

            pedido.setUsuario(usuario);
            listaSequencia.add(pedido);
        }

        stmt.close();
        con.close();

        return listaSequencia;
    }

    /*
     atualiza grafico com comparativo mensal entre vendedores por período
     */
    public static List<PedidoBean> grafLinhaTempoMensalVendedor(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {

        List<PedidoBean> listaSequencia = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, C04NOME, SUM(C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER) AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "INNER JOIN SPAC04 ON (C13VEND = C04CODIGO) \n"
                + "WHERE \n"
                + "C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "GROUP BY 1, 2 ";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setData(rs.getString("MES"));
            pedido.setvFinal(rs.getDouble("VRFINAL"));

            UsuarioBean usuario = new UsuarioBean();

            usuario.setNome(rs.getString("C04NOME"));

            pedido.setUsuario(usuario);
            listaSequencia.add(pedido);
        }

        stmt.close();
        con.close();

        return listaSequencia;
    }

    /*
     atualiza grafico com total de vendas da empresa por dia
     */
    public static List<PedidoBean> grafVendaDiariaEmpresa(String dataInicial, String dataFinal) throws SQLException, IOException {

        List<PedidoBean> listaSequencia = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, SUM(C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER) AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "WHERE \n"
                + "C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "GROUP BY 1";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setData(Utils.dataCorreta(rs.getString("C13DATA")));
            pedido.setvFinal(rs.getDouble("VRFINAL"));

            listaSequencia.add(pedido);
        }

        stmt.close();
        con.close();

        return listaSequencia;
    }

    /*
     atualiza grafico com total de vendas da empresa por mês e ano
     */
    public static List<PedidoBean> grafLinhaTempoEmpresaMensall(String dataInicial, String dataFinal) throws SQLException, IOException {

        List<PedidoBean> listaSequencia = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, SUBSTRING(C13DATA,1,4) AS ANO,\n"
                + "SUM(C13VRTOT - C13VDESFPG - C13DESCGER + C13VACRFPG + C13ACREGER) AS VRFINAL \n"
                + "FROM SPAC13 \n"
                + "WHERE C13TIPO = 'P' \n"
                + "AND C13SITU = '' \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "GROUP BY 1, 2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setMes(rs.getString("MES"));
            pedido.setAno(rs.getString("ANO"));
            pedido.setvFinal(rs.getDouble("VRFINAL"));

            listaSequencia.add(pedido);
        }

        stmt.close();
        con.close();

        return listaSequencia;
    }

    /*
     * lista com dados para atualizar analise de pedido
     */
    public static List<PedidoBean> listaDados(String nPedido) throws SQLException, IOException {
        List<PedidoBean> lista = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C14PROD, C01DESC, C14QTD, C01PRCCUS, C14VRUNIT, "
                + "ROUND((((C14VRUNIT / C01PRCCUS)-1)*100),3) AS MARKUP, "
                + "ROUND(((C14VRUNIT-C01PRCCUS)/C14VRUNIT)*100,3) AS MARGEM_BRUTA "
                + "FROM SPAC01, SPAC14 "
                + "WHERE "
                + "C14PROD = C01CODIGO AND "
                + "C14NPED = '" + nPedido + "'";

        PreparedStatement stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            ProdutoBean produto = new ProdutoBean();
            produto.setCodigo(rs.getString("C14PROD"));
            produto.setDescricao(rs.getString("C01DESC"));
            produto.setpCusto(rs.getDouble("C01PRCCUS"));
            produto.setMarkup(rs.getDouble("MARKUP"));
            produto.setMargem(rs.getDouble("MARGEM_BRUTA"));

            ProdutoPedidoBean iPedido = new ProdutoPedidoBean();
            iPedido.setQtdProdutoCompra(rs.getDouble("C14QTD"));
            iPedido.setPvenda(rs.getDouble("C14VRUNIT"));

            pedido.setProduto(produto);
            pedido.setiPedido(iPedido);

            lista.add(pedido);

        }

        stm.close();
        con.close();
        return lista;
    }

    /*
     * total de desconto do pedido. Para Analise de Pedidos
     */
    public static double desconto(String nPedido, String tipo) throws SQLException, IOException {

        double valor = 0.0;
        Connection con = ConexaoADS.getConection();
        String sql = "";
        if (tipo.equals("P")) {
            sql = "SELECT C13DESCGER+C13VDESESP+C12DESCTO AS DESCONTO FROM SPAC13 \n"
                    + "INNER JOIN SPAC12 ON (C13NPED = C12NRDOC) \n"
                    + "WHERE C13NPED = '" + nPedido + "' ";
        } else {
            sql = "SELECT C13DESCGER+C13VDESESP AS DESCONTO FROM SPAC13 \n"
                    + "WHERE C13NPED = '" + nPedido + "' ";
        }

        PreparedStatement stm;
        stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            valor = rs.getDouble("DESCONTO");

        }

        stm.close();
        con.close();
        return valor;
    }


    /*
     * total de acrescimo do pedido. Para Analise de Pedidos
     */
    public static double acrescimo(String nPedido) throws SQLException, IOException {

        double valor = 0.0;
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13ACREGER, C13VIPI, C13FRETE  FROM SPAC13 WHERE C13NPED = '" + nPedido + "'";

        PreparedStatement stm;
        stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            valor += rs.getDouble("C13ACREGER");
            valor += rs.getDouble("C13VIPI");
            valor += rs.getDouble("C13FRETE");

        }

        stm.close();
        con.close();

        return valor;
    }

    /*
     * retorna data e tipo, se é orçamento ou pedido
     */
    public static List<PedidoBean> tipoData(String codigo) throws SQLException, IOException {

        List<PedidoBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13TIPO, C13DATA FROM SPAC13 WHERE C13NPED = '" + codigo + "'";

        PreparedStatement stm = con.prepareStatement(sql);
        ResultSet rs = stm.executeQuery();

        while (rs.next()) {
            PedidoBean pedido = new PedidoBean();
            pedido.setTipo(rs.getString("C13TIPO"));
            pedido.setData(rs.getString("C13DATA"));

            lista.add(pedido);
        }

        stm.close();
        con.close();

        return lista;
    }

    /*
     metodo utilizado para listar os descontos concedidos no período
     */
    public static List<PedidoBean> descontoPeriodo(String dataInicial, String dataFinal, String idVendedor) throws SQLException, IOException {
        List<PedidoBean> lista = new ArrayList<>();
        Connection con;

        con = ConexaoADS.getConection();

        String sql = "SELECT C13NPED, C04NOME, C04CODIGO, C13DATA, C05NOME, C13VRTOT, C13DESCGER,C13VDESESP,"
                + "SUM(C13VRTOT - C13DESCGER) AS VALORFINAL,"
                + "ROUND ((((SUM(C13VRTOT - (C13DESCGER+C13VDESESP))*100)/C13VRTOT)-100),2) AS PORCENTAGEM "
                + "FROM SPAC04, SPAC05, SPAC13 "
                + "WHERE C13CLIE = C05CODIGO "
                + "AND C13VEND = C04CODIGO "
                + "AND "
                + "C13TIPO = 'P' AND "
                + "C13SITU = '' AND "
                + "C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' "
                + "AND C04CODIGO LIKE '%" + idVendedor + "%' "
                + "GROUP BY C13NPED, C13DATA, C13FPAGTO, C13VRTOT, C13DESCGER, C13VDESESP, C05NOME, C04NOME, C04CODIGO "
                + "ORDER BY 10 ASC";

        PreparedStatement stm = con.prepareStatement(sql);

        ResultSet rs = stm.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();

            pedido.setPedido(rs.getString("C13NPED"));
            pedido.setData(rs.getString("C13DATA"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            usuario.setCodigo(rs.getString("C04CODIGO"));

            pedido.setUsuario(usuario);

            ClienteBean cliente = new ClienteBean();
            cliente.setNome(rs.getString("C05NOME"));

            pedido.setCliente(cliente);

            pedido.setvTotal(rs.getDouble("C13VRTOT"));
            pedido.setdGerente(rs.getDouble("C13DESCGER"));
            pedido.setdEspecial(rs.getDouble("C13VDESESP"));
            pedido.setvFinal(rs.getDouble("VALORFINAL"));
            pedido.setPorcentagem(rs.getDouble("PORCENTAGEM"));

            lista.add(pedido);

        }

        stm.close();
        con.close();

        return lista;
    }

    /*
     lista com os clientes que mais compraram no período
     */
    public static List<PedidoBean> topClientesPeriodo(String dtInicial, String dtFinal, int qtdClientes) throws SQLException, IOException {
        List<PedidoBean> listaTopClientes = new ArrayList<>();

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT TOP " + qtdClientes + " C13CLIE, C05NOME, SUM(ROUND(C13VRTOT + ( C13ACREGER+C13VACRFPG) - ( C13DESCGER+C13VRVALE+C13VDESFPG+C13VDESESP),2)) AS VRFINAL, COUNT(C13VRTOT) AS QTDPEDIDOS \n"
                + "FROM SPAC05, SPAC13 \n"
                + "WHERE C13CLIE = C05CODIGO AND\n"
                + "C13TIPO = 'P' AND "
                + "C13SITU = '' AND "
                + "C13DATA BETWEEN '" + dtInicial + "' AND '" + dtFinal + "' \n"
                + "GROUP BY C13CLIE, C05NOME \n"
                + "ORDER BY 3 DESC ";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            PedidoBean pedido = new PedidoBean();
            pedido.setvFinal(rs.getDouble("VRFINAL"));

            ClienteBean cliente = new ClienteBean();
            cliente.setCodigo(rs.getString("C13CLIE"));
            cliente.setNome(rs.getString("C05NOME"));

            pedido.setCliente(cliente);
            pedido.setQtdPedidos(rs.getInt("QTDPEDIDOS"));

            listaTopClientes.add(pedido);
        }

        stmt.close();
        con.close();

        return listaTopClientes;
    }

}
